package mysql

import (
	// "bufio"
	"database/sql"
	"fmt"
	// "log"
	"math/rand"
	// "os"
	"src/command"
	"strconv"
	"strings"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

type Client struct {
	connstr string
	driver  string
	dbconn  *sql.DB
}

func NewClient(user, pass, dbname, protocol string) *Client {
	connstr := fmt.Sprintf("%s:%s@%s/%s?charset=utf8", user, pass, protocol, dbname)
	return &Client{connstr, "mysql", nil}
}

func (cli *Client) Conn() error {
	db, err := sql.Open(cli.driver, cli.connstr)
	if err != nil {
		fmt.Println("\rfailed to open database ", err)
		return err
	}
	cli.dbconn = db
	return cli.dbconn.Ping()
}

func (cli *Client) exec_sql(xsql string) {
	result, err := cli.dbconn.Exec(xsql)
	if err != nil {
		fmt.Println("\rfailed to exec sql:", xsql, err)
		return
	}
	rowsaff, _ := result.RowsAffected()
	fmt.Println("\rRowsAffected:", rowsaff)
}

func (cli *Client) query_sql(xsql string) {
	rows, err := cli.dbconn.Query(xsql)
	if err != nil {
		fmt.Println("\rfailed for query sql:", err)
		return
	}
	cols, err := rows.Columns()
	if err != nil {
		fmt.Println("\rfailed for get columns:", err)
		return
	}
	colCount := len(cols)

	//fmt.Println(colCount, "\n", cols)
	fmt.Print("\r")
	for _, v := range cols {
		fmt.Printf("%s\t", v)
	}
	fmt.Println("\r\n----------------------------------------")

	values := make([]sql.NullString, colCount)
	oneRows := make([]interface{}, colCount)
	for k, _ := range values {
		oneRows[k] = &values[k] //将查询结果的返回地址绑定，这样才能变参获取数据
	}

	for rows.Next() {

		//扫描结果集，一定要在Next调用后，方可使用
		err = rows.Scan(oneRows...)
		if err != nil {
			fmt.Println("\rfailed to Scan result set", err)
			break
		}
		//fmt.Println(values)
		fmt.Print("\r")
		for _, v := range values {
			if v.Valid {
				fmt.Printf("%s\t", v.String)
			} else {
				fmt.Printf("%s\t", "NULL")
			}
		}
		fmt.Println("\r")
	}
}

//*********************************************************************************************************
const (
	MaxAttrNum = 100
)

type WaterAlarmInfo struct {
	xtype     int
	last_val  [MaxAttrNum]float32
	last_bool [MaxAttrNum]bool
}

type WaterItem struct {
	Sn              uint64 `json:"sn"`
	Wateraffairs_id uint64 `json:"wateraffairs_id"`
}

var levelArray = [3]string{
	"general",
	"important",
	"emergency",
}
var (
	is_alarming  int64  = 1
	alarm_type   int64  = 1
	alarm_id     int    = 1
	alarm_magic  string = "magic_test"
	device_magic int64  = 0xaabbccdd
)

func (w *WaterAlarmInfo) GetInfoByIndex(id int) (int, int, string, string, string) {
	var wid int = 0
	var iid int = 0
	var level string = ""
	var value string = ""
	var reason string = ""
	var min float32 = 0
	var max float32 = 0
	var diff float32 = 0
	var step float32 = 0
	var probability int = 10

	//3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,这些做值翻转就行了
	switch id {
	case 1:
		//余氯值（浮点）
		wid = 7
		iid = id
		min = 0.3
		max = 0.6
		diff = max - min
		step = diff / 10
		//判断下有没有初始化上次的值
		for w.last_val[id] == 0 {
			if w.last_val[id] != 0 {
				break
			}
			w.last_val[id] = rand.Float32()*diff + min
		}
		if rand.Intn(2) == 0 {
			w.last_val[id] -= step
		} else {
			w.last_val[id] += step
		}
		if w.last_val[id] > max {
			w.last_val[id] = max
		}
		if w.last_val[id] < min {
			w.last_val[id] = min
		}
		value = fmt.Sprintf("%.4f", w.last_val[id])
	case 2:
		//原水浊度
		wid = 1
		iid = id
		min = 0
		max = 500
		diff = max - min
		step = diff / 10
		//判断下有没有初始化上次的值
		for w.last_val[id] == 0 {
			if w.last_val[id] != 0 {
				break
			}
			w.last_val[id] = rand.Float32()*diff + min
		}
		if rand.Intn(2) == 0 {
			w.last_val[id] -= step
		} else {
			w.last_val[id] += step
		}
		if w.last_val[id] > max {
			w.last_val[id] = max
		}
		if w.last_val[id] < min {
			w.last_val[id] = min
		}
		value = fmt.Sprintf("%.4f", w.last_val[id])
	case 3:
		//1#排污阀已开
		wid = 2
		iid = id
		// level = levelArray[rand.Intn(3)]
		// value = fmt.Sprintf("%d", (rand.Intn(probability)))
		if w.last_bool[id] {
			value = "0"
		} else {
			value = "1"
		}

		if value == "0" {
			w.last_bool[id] = false
		} else {
			reason = "1#排污阀开启"
			level = levelArray[0]
			w.last_bool[id] = true
			value = "1"
		}
	case 4:
		//1#排污阀已关
		wid = 2
		iid = id
		// level = levelArray[rand.Intn(3)]
		if !w.last_bool[id-1] {
			reason = "1#排污阀已关闭"
			level = levelArray[1]
			value = "1"
		} else {
			value = "0"
		}
	case 5:
		//2#排污阀开启
		wid = 2
		iid = id
		// level = levelArray[rand.Intn(3)]
		// value = fmt.Sprintf("%d", (rand.Intn(probability)))
		if w.last_bool[id] {
			value = "0"
		} else {
			value = "1"
		}

		if value == "0" {
			w.last_bool[id] = false
		} else {
			w.last_bool[id] = true
			value = "1"
		}
	case 6:
		//2#排污阀关闭
		wid = 2
		iid = id
		if w.last_bool[id-1] == false {
			value = "1"
		} else {
			value = "0"
		}
	case 7:
		//进水开阀启动
		wid = 1
		iid = id
		// value = fmt.Sprintf("%d", (rand.Intn(probability)))
		if w.last_bool[id] {
			value = "0"
		} else {
			value = "1"
		}

		if value == "0" {
			w.last_bool[id] = false
		} else {
			reason = "进水开阀,设备启动运行"
			level = levelArray[1]
			w.last_bool[id] = true
			value = "1"
		}
	case 8:
		//进水关阀关闭
		wid = 1
		iid = id
		if !w.last_bool[id-1] {
			reason = "进水关阀,设备待机等待"
			level = levelArray[1]
			value = "1"
		} else {
			value = "0"
		}
	case 9:
		//加药桶低液位
		wid = 4
		iid = id
		// value = fmt.Sprintf("%d", (rand.Intn(probability)))
		if w.last_bool[id] {
			value = "0"
		} else {
			value = "1"
		}

		if value == "0" {
			w.last_bool[id] = false
		} else {
			reason = "加药桶低液位报警触发"
			level = levelArray[0]
			w.last_bool[id] = true
			value = "1"
		}
	case 10:
		//消毒药剂低液位
		wid = 5
		iid = id
		// value = fmt.Sprintf("%d", (rand.Intn(probability)))
		if w.last_bool[id] {
			value = "0"
		} else {
			value = "1"
		}

		if value == "0" {
			w.last_bool[id] = false
		} else {
			reason = "消毒药剂低液位报警触发"
			level = levelArray[0]
			w.last_bool[id] = true
			value = "1"
		}
	case 11:
		//清水池低液位
		wid = 6
		iid = id
		// value = fmt.Sprintf("%d", (rand.Intn(probability)))
		if w.last_bool[id] {
			value = "0"
		} else {
			value = "1"
		}

		if value == "0" {
			w.last_bool[id] = false
		} else {
			reason = "清水池低液位触发"
			level = levelArray[0]
			w.last_bool[id] = true
			value = "1"
		}
	case 12:
		//排污阀切换反馈
		wid = 2
		iid = id
		// value = fmt.Sprintf("%d", (rand.Intn(probability)))
		if w.last_bool[id] {
			value = "0"
		} else {
			value = "1"
		}

		if value == "0" {
			w.last_bool[id] = false
		} else {
			w.last_bool[id] = true
			value = "1"
		}
	case 13:
		//自动运行信号反馈
		wid = 14
		iid = id
		// value = fmt.Sprintf("%d", (rand.Intn(probability)))
		if w.last_bool[id] {
			value = "0"
		} else {
			value = "1"
		}

		if value == "0" {
			w.last_bool[id] = false
			value = "1"
		} else {
			reason = "非自动运行状态触发(已应急模式)"
			level = levelArray[2]
			w.last_bool[id] = true
			value = "0"
		}
	case 14:
		//自动反冲洗反馈
		wid = 3
		iid = id
		// value = fmt.Sprintf("%d", (rand.Intn(probability)))
		if w.last_bool[id] {
			value = "0"
		} else {
			value = "1"
		}

		if value == "0" {
			w.last_bool[id] = false
		} else {
			reason = "自动反冲洗运行触发"
			level = levelArray[1]
			w.last_bool[id] = true
			value = "1"
		}
	case 15:
		//排污开阀
		wid = 2
		iid = id
		value = fmt.Sprintf("%d", (rand.Intn(probability)))

		if value == "0" {
			w.last_bool[id] = false
		} else {
			w.last_bool[id] = true
			value = "1"
		}
	case 16:
		//排污关阀
		wid = 2
		iid = id
		if w.last_bool[id-1] == false {
			value = "1"
		} else {
			value = "0"
		}
	case 17:
		//进水开阀
		wid = 1
		iid = id
		value = fmt.Sprintf("%d", (rand.Intn(probability)))

		if value == "0" {
			w.last_bool[id] = false
		} else {
			w.last_bool[id] = true
			value = "1"
		}
	case 18:
		//进水关阀
		wid = 1
		iid = id
		if w.last_bool[id-1] == false {
			value = "1"
		} else {
			value = "0"
		}
	case 19:
		//1#加药泵
		wid = 4
		iid = id
		value = fmt.Sprintf("%d", (rand.Intn(probability)))

		if value == "0" {
			w.last_bool[id] = false
		} else {
			w.last_bool[id] = true
			value = "1"
		}
	case 20:
		//2#加药泵
		wid = 4
		iid = id
		if w.last_bool[id-1] == false {
			value = "1"
		} else {
			value = "0"
		}
	case 21:
		//消毒药剂泵
		wid = 5
		iid = id
		value = fmt.Sprintf("%d", (rand.Intn(probability)))

		if value == "0" {
			w.last_bool[id] = false
		} else {
			w.last_bool[id] = true
			value = "1"
		}
	case 22:
		//排污阀执行切换
		wid = 2
		iid = id
		value = fmt.Sprintf("%d", (rand.Intn(probability)))

		if value == "0" {
			w.last_bool[id] = false
		} else {
			w.last_bool[id] = true
			value = "1"
		}
	case 23:
		//出水浊度
		wid = 7
		iid = id
		min = 0
		max = 200
		diff = max - min
		step = diff / 10
		//判断下有没有初始化上次的值
		for w.last_val[id] == 0 {
			if w.last_val[id] != 0 {
				break
			}
			w.last_val[id] = rand.Float32()*diff + min
		}
		if rand.Intn(2) == 0 {
			w.last_val[id] -= step
		} else {
			w.last_val[id] += step
		}
		if w.last_val[id] > max {
			w.last_val[id] = max
		}
		if w.last_val[id] < min {
			w.last_val[id] = min
		}
		value = fmt.Sprintf("%.4f", w.last_val[id])
	case 24:
		//加药泵脉冲频率
		wid = 4
		iid = id
		min = 20
		max = 100
		diff = max - min
		step = diff / 10
		//判断下有没有初始化上次的值
		for w.last_val[id] == 0 {
			if w.last_val[id] != 0 {
				break
			}
			w.last_val[id] = rand.Float32()*diff + min
		}
		if rand.Intn(2) == 0 {
			w.last_val[id] -= step
		} else {
			w.last_val[id] += step
		}
		if w.last_val[id] > max {
			w.last_val[id] = max
		}
		if w.last_val[id] < min {
			w.last_val[id] = min
		}
		value = fmt.Sprintf("%.4f", w.last_val[id])
	case 25:
		//余氯泵脉冲频率
		wid = 6
		iid = id
		min = 20
		max = 100
		diff = max - min
		step = diff / 10
		//判断下有没有初始化上次的值
		for w.last_val[id] == 0 {
			if w.last_val[id] != 0 {
				break
			}
			w.last_val[id] = rand.Float32()*diff + min
		}
		if rand.Intn(2) == 0 {
			w.last_val[id] -= step
		} else {
			w.last_val[id] += step
		}
		if w.last_val[id] > max {
			w.last_val[id] = max
		}
		if w.last_val[id] < min {
			w.last_val[id] = min
		}
		value = fmt.Sprintf("%.4f", w.last_val[id])
	case 26:
		//排污计算时显示
		wid = 2
		iid = id
		min = 0
		max = 1440
		diff = max - min
		step = diff / 10
		//判断下有没有初始化上次的值
		for w.last_val[id] == 0 {
			if w.last_val[id] != 0 {
				break
			}
			w.last_val[id] = rand.Float32()*diff + min
		}
		if rand.Intn(2) == 0 {
			w.last_val[id] -= step
		} else {
			w.last_val[id] += step
		}
		if w.last_val[id] > max {
			w.last_val[id] = max
		}
		if w.last_val[id] < min {
			w.last_val[id] = min
		}
		value = fmt.Sprintf("%.4f", w.last_val[id])
	case 27:
		//转换
		wid = 14
		iid = id
		value = fmt.Sprintf("%d", (rand.Intn(probability)))

		if value == "0" {
			w.last_bool[id] = false
		} else {
			w.last_bool[id] = true
			value = "1"
		}
	default:
	}

	return wid, iid, level, value, reason
}

func (cli *Client) device_status_history_add_ext() {
	alarm_id = 0
	cli.device_status_history_add1()
	cli.device_status_history_add2()
}

func (cli *Client) device_status_history_add1() {
	var begin_time time.Time = time.Date(g_conf.Alarm_begin_year, time.Month(g_conf.Alarm_begin_month), g_conf.Alarm_begin_day, g_conf.Alarm_begin_hour, g_conf.Alarm_begin_min, 00, 00, time.Local) //开始时间
	var end_time time.Time = time.Date(g_conf.Alarm_end_year, time.Month(g_conf.Alarm_end_month), g_conf.Alarm_end_day, g_conf.Alarm_end_hour, g_conf.Alarm_end_min, 00, 00, time.Local)             //结束时间,或者time.Now()
	var interval_time int64 = int64(g_conf.Alarm_interval)
	//设备状态记录时间间隔最小时间5分钟,float类型
	var sa []int = []int{1, 2, 23, 24, 25}
	for i := 0; i < len(g_conf.Line_num); i++ {
		cli.device_status_history_add(begin_time, end_time, interval_time, sa, g_conf.Device_table, g_conf.Alarm_table, g_conf.Line_num[i])
	}
}

func (cli *Client) device_status_history_add2() {
	var begin_time time.Time = time.Date(g_conf.Status_begin_year, time.Month(g_conf.Status_begin_month), g_conf.Status_begin_day, g_conf.Status_begin_hour, g_conf.Status_begin_min, 00, 00, time.Local) //开始时间
	var end_time time.Time = time.Date(g_conf.Status_end_year, time.Month(g_conf.Status_end_month), g_conf.Status_end_day, g_conf.Status_end_hour, g_conf.Status_end_min, 00, 00, time.Local)             //结束时间,或者time.Now()
	var interval_time int64 = int64(g_conf.Status_interval)
	//设备状态记录时间间隔最小时间4小时,bool类型
	var sa []int = []int{3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14}
	for i := 0; i < len(g_conf.Line_num); i++ {
		cli.device_status_history_add(begin_time, end_time, interval_time, sa, g_conf.Device_table_bool, g_conf.Alarm_table, g_conf.Line_num[i])
	}
}

type LastAlarm struct {
	valid   bool
	last_id int
}

func (cli *Client) device_status_history_add(begin_time time.Time, end_time time.Time, device_interval_min_time_sec int64, sa []int, status_table string, alarm_table string, line_num int) {
	btime := begin_time.Unix()
	etime := end_time.Unix()

	str := ""
	var build strings.Builder
	var max_add int = 30000
	var add_index int = 0

	var alarm_max_add int = 30000
	var alarm_add_index int = 0
	var alarm_build strings.Builder

	var alarm_num int = 0
	var status_num int = 0

	fmt.Println("\rgoto device_status_history_add")
	waterarraynum := len(g_conf.WaterArray)
	ai := make([]WaterAlarmInfo, waterarraynum)
	last_alarm := make([][MaxAttrNum]LastAlarm, waterarraynum)
	for btime <= etime {
		for i := 0; i < waterarraynum; i++ {
			sn := g_conf.WaterArray[i].Sn
			wateraffairs_id := g_conf.WaterArray[i].Wateraffairs_id
			year := 0
			month := 0
			day := 0
			t := time.Unix(btime, 0)
			year = t.Year()
			month = int(t.Month())
			day = t.Day()
			dsttime := t.Format(g_conf.TimeLayout)
			for j := 0; j < len(sa); j++ {
				work_part_id, alarm_items_id, alarm_level, alarm_value, alarm_reason := ai[i].GetInfoByIndex(sa[j])
				dstvalue, _ := strconv.ParseFloat(alarm_value, 64)
				//先传alarm,要计算alarm_id
				device_alarm_id := 0
				if alarm_reason != "" {
					//表示有报警,需要添加报警
					//这里判断下,如果是连贯的报警,就不要添加新的报警信息了,直接用上次的
					if last_alarm[i][j].valid == false {
						device_alarm_id = alarm_id
						last_alarm[i][j].last_id = alarm_id
						if alarm_add_index == 0 {
							str = fmt.Sprintf("(%d, %d, %d, %d, %d, %d, %d, '%s', '%s', '%s', '%s', %d, %d, %d, '%s')", sn, is_alarming, alarm_type, alarm_items_id, wateraffairs_id, line_num, work_part_id, dsttime, alarm_level, alarm_value, alarm_reason, year, month, day, alarm_magic)
						} else {
							str = fmt.Sprintf(",(%d, %d, %d, %d, %d, %d, %d, '%s', '%s', '%s', '%s', %d, %d, %d, '%s')", sn, is_alarming, alarm_type, alarm_items_id, wateraffairs_id, line_num, work_part_id, dsttime, alarm_level, alarm_value, alarm_reason, year, month, day, alarm_magic)
						}
						alarm_add_index++
						alarm_id++
						alarm_num++
						alarm_build.WriteString(str)
					} else {
						//是连续添加的,则用上次的id
						device_alarm_id = last_alarm[i][j].last_id
					}
					last_alarm[i][j].valid = true
				} else {
					last_alarm[i][j].valid = false
				}
				if add_index == 0 {
					str = fmt.Sprintf("(%d, %d, %d, %d, '%s', %d, %f, %d, %d, %d, %d)", sn, wateraffairs_id, line_num, work_part_id, dsttime, alarm_items_id, dstvalue, year, month, day, device_alarm_id)
				} else {
					str = fmt.Sprintf(",(%d, %d, %d, %d, '%s', %d, %f, %d, %d, %d, %d)", sn, wateraffairs_id, line_num, work_part_id, dsttime, alarm_items_id, dstvalue, year, month, day, device_alarm_id)
				}
				add_index++
				status_num++
				build.WriteString(str)
			}
		}
		if add_index >= max_add {
			fmt.Println("\rgoto add device_status table=", status_table)
			dststr := fmt.Sprintf("insert into %s(sn, wateraffairs_id, line_num, work_part_id, push_time, items_id, value, year_int, month_int, day_int, alarm_id) values %s", status_table, build.String())
			cli.exec_sql(dststr)
			add_index = 0
			build.Reset()
		}
		if alarm_add_index >= alarm_max_add {
			fmt.Println("\rgoto add device_alarm alarm_table=", alarm_table)
			dststr := fmt.Sprintf("insert into %s(sn, is_alarming, alarm_type, alarm_items_id, wateraffairs_id, line_num, work_part_id, alarm_time, alarm_level, alarm_value, alarm_reason, year_int, month_int, day_int, processor) values %s", alarm_table, alarm_build.String())
			cli.exec_sql(dststr)
			alarm_add_index = 0
			alarm_build.Reset()
		}
		btime += device_interval_min_time_sec
	}
	if add_index > 0 {
		fmt.Println("\rgoto add device_status status_table=", status_table)
		dststr := fmt.Sprintf("insert into %s(sn, wateraffairs_id, line_num, work_part_id, push_time, items_id, value, year_int, month_int, day_int, alarm_id) values %s", status_table, build.String())
		cli.exec_sql(dststr)
		add_index = 0
		build.Reset()
	}
	if alarm_add_index > 0 {
		fmt.Println("\rgoto add device_alarm alarm_table=", alarm_table)
		dststr := fmt.Sprintf("insert into %s(sn, is_alarming, alarm_type, alarm_items_id, wateraffairs_id, line_num, work_part_id, alarm_time, alarm_level, alarm_value, alarm_reason, year_int, month_int, day_int, processor) values %s", alarm_table, alarm_build.String())
		cli.exec_sql(dststr)
		alarm_add_index = 0
		alarm_build.Reset()
	}
	fmt.Println("\rexit device_status_history_add alarm_num=", alarm_num, " status_num=", status_num)
}

type alarm_info struct {
	work_part_id   int
	alarm_items_id int
	reason         string
	reason1        string
}

func (cli *Client) device_status_alarm_history_add(line_num int) {
	// var begin_time time.Time = time.Date(2020, 04, 14, 00, 00, 00, 00, time.Local) //开始时间
	// var end_time time.Time = time.Date(2020, 05, 31, 23, 00, 00, 00, time.Local)   //结束时间,或者time.Now()
	var begin_time time.Time = time.Date(g_conf.Alarm_begin_year, time.Month(g_conf.Alarm_begin_month), g_conf.Alarm_begin_day, g_conf.Alarm_begin_hour, g_conf.Alarm_begin_min, 00, 00, time.Local) //开始时间
	var end_time time.Time = time.Date(g_conf.Alarm_end_year, time.Month(g_conf.Alarm_end_month), g_conf.Alarm_end_day, g_conf.Alarm_end_hour, g_conf.Alarm_end_min, 00, 00, time.Local)             //结束时间,或者time.Now()
	btime := int(begin_time.Unix())
	etime := int(end_time.Unix())
	oneday := 24 * 60 * 60

	var alarm_max_add int = 1 //30000
	var alarm_add_index int = 0
	var alarm_build strings.Builder

	rand.Seed(time.Now().Unix())
	fmt.Println("\rgoto device_status_alarm_history_add")

	rand_num := 0
	min := 3
	max := 10
	diff := max - min
	nexttime := 0
	// 加药箱异常开启		work_part_id=8	alarm_items_id(items_id)=22		value=1		alarm_image="" (每天生成3~9个)
	// 配电箱异常开启		work_part_id=9	alarm_items_id(items_id)=24		value=1		alarm_image="" (每天生成3~9个)
	// 操作间有人闯入		work_part_id=11	alarm_items_id(items_id)=30		value=1		alarm_image="" (每天生成3~9个)
	// 机房有人闯入		work_part_id=13	alarm_items_id(items_id)=31		value=1		alarm_image="" (每天生成3~9个)
	alarm_type_array := [4]alarm_info{
		{8, 22, "加药箱异常开启", "加药箱异常开启"},
		{9, 24, "配电箱异常开启", "配电箱异常开启"},
		{11, 30, "操作间红外探测到有人闯入", "操作间摄像头抓拍到有人闯入"},
		{13, 31, "机房红外探测到有人闯入", "机房摄像头抓拍到有人闯入"}}
	for i := 0; i < len(g_conf.WaterArray); i++ {
		nexttime = btime
		sn := g_conf.WaterArray[i].Sn
		wateraffairs_id := g_conf.WaterArray[i].Wateraffairs_id
		alarm_add_index = 0
		//每个设备来一下
		for nexttime <= etime {
			for n := 0; n < len(alarm_type_array); n++ {
				work_part_id := alarm_type_array[n].work_part_id
				alarm_items_id := alarm_type_array[n].alarm_items_id
				reason := alarm_type_array[n].reason
				if rand.Intn(2) == 1 {
					reason = alarm_type_array[n].reason1
				}
				rand_num = rand.Intn(diff) + min
				for j := 0; j < rand_num; j++ {
					// alarm_image := ""
					alarm_value := "1"
					atime := int(rand.Intn(oneday))
					t := time.Unix(int64(atime+nexttime), 0)
					year := t.Year()
					month := int(t.Month())
					day := t.Day()
					dsttime := t.Format(g_conf.TimeLayout)
					str := ""
					if alarm_add_index == 0 {
						str = fmt.Sprintf("(%d, %d, %d, %d, %d, %d, %d, '%s', '%s', %d, %d, %d, '%s')", sn, is_alarming, alarm_type, alarm_items_id, wateraffairs_id, line_num, work_part_id, dsttime, alarm_value, year, month, day, reason)
					} else {
						str = fmt.Sprintf(",(%d, %d, %d, %d, %d, %d, %d, '%s', '%s', %d, %d, %d, '%s')", sn, is_alarming, alarm_type, alarm_items_id, wateraffairs_id, line_num, work_part_id, dsttime, alarm_value, year, month, day, reason)
					}
					alarm_add_index++
					alarm_build.WriteString(str)
				}
			}

			nexttime += oneday
		}
		if alarm_add_index >= alarm_max_add {
			fmt.Println("\rgoto add device_alarm")
			dststr := fmt.Sprintf("insert into %s(sn, is_alarming, alarm_type, alarm_items_id, wateraffairs_id, line_num, work_part_id, alarm_time, alarm_value, year_int, month_int, day_int, alarm_reason) values %s", g_conf.Alarm_table, alarm_build.String())
			cli.exec_sql(dststr)
			alarm_add_index = 0
			alarm_build.Reset()
		}
	}
	if alarm_add_index > 0 {
		fmt.Println("\rgoto add device_alarm")
		dststr := fmt.Sprintf("insert into %s(sn, is_alarming, alarm_type, alarm_items_id, wateraffairs_id, line_num, work_part_id, alarm_time, alarm_value, year_int, month_int, day_int, alarm_reason) values %s", g_conf.Alarm_table, alarm_build.String())
		fmt.Println("\rdststr=", dststr)
		cli.exec_sql(dststr)
		alarm_add_index = 0
		alarm_build.Reset()
	}
}

func (cli *Client) device_curstatus_add() {
	var cur_now int = int(time.Now().Unix())

	var status_max_add int = 30000
	var status_add_index int = 0
	var status_build strings.Builder

	rand.Seed(time.Now().Unix())
	fmt.Println("\rgoto device_curstatus_add")
	var l_is_alarming int = 0
	var l_status int = 1
	var l_value string = ""
	var ra = make([]WaterAlarmInfo, len(g_conf.WaterArray))
	var nexttime int = 0
	status_add_index = 0
	for li := 0; li < len(g_conf.Line_num); li++ {
		l_line_num := g_conf.Line_num[li]
		for i := 0; i < len(g_conf.WaterArray); i++ {
			nexttime = cur_now
			sn := g_conf.WaterArray[i].Sn
			wateraffairs_id := g_conf.WaterArray[i].Wateraffairs_id
			t := time.Unix(int64(nexttime), 0)
			//每个设备来一下
			for j := 0; j < 27; j++ {
				_, _, _, l_value, _ = ra[i].GetInfoByIndex(j + 1)
				dsttime := t.Format(g_conf.TimeLayout)
				str := ""
				if status_add_index == 0 {
					str = fmt.Sprintf("(%d, %d, %d, %d, '%s', %d, %d, '%s')", sn, wateraffairs_id, l_line_num, j+1, l_value, l_is_alarming, l_status, dsttime)
				} else {
					str = fmt.Sprintf(",(%d, %d, %d, %d, '%s', %d, %d, '%s')", sn, wateraffairs_id, l_line_num, j+1, l_value, l_is_alarming, l_status, dsttime)
				}
				status_add_index++
				status_build.WriteString(str)
			}
			if status_add_index >= status_max_add {
				fmt.Println("\rgoto add device_alarm")
				dststr := fmt.Sprintf("insert into %s(sn, wateraffairs_id, line_num, items_id, value, is_alarming, status, update_time) values %s", g_conf.Status_table, status_build.String())
				cli.exec_sql(dststr)
				status_add_index = 0
				status_build.Reset()
			}
		}
	}

	if status_add_index > 0 {
		fmt.Println("\rgoto add device_alarm")
		dststr := fmt.Sprintf("insert into %s(sn, wateraffairs_id, line_num, items_id, value, is_alarming, status, update_time) values %s", g_conf.Status_table, status_build.String())
		cli.exec_sql(dststr)
		status_add_index = 0
		status_build.Reset()
	}
}

func (cli *Client) help() {
	fmt.Println("\rUsage:")
	fmt.Println("\r\t device_add")
	fmt.Println("\r\t device_alarm_add")
	fmt.Println("\r\t quit")
}

//****************************注册进通用操作命令中去********************************************************
func go_device_add(v ...interface{}) bool {
	if g_client == nil {
		fmt.Println("\rplease connect first")
		return false
	}

	g_client.device_status_history_add_ext()

	return true
}

func go_device_alarm_add(v ...interface{}) bool {
	if g_client == nil {
		fmt.Println("\rplease connect first")
		return false
	}

	for i := 0; i < len(g_conf.Line_num); i++ {
		g_client.device_status_alarm_history_add(g_conf.Line_num[i])
	}

	return true
}

func go_device_cur_status_add(v ...interface{}) bool {
	if g_client == nil {
		fmt.Println("\rplease connect first")
		return false
	}

	g_client.device_curstatus_add()

	return true
}

func go_device_mysql_clean(v ...interface{}) bool {
	if g_client == nil {
		fmt.Println("\rplease connect first")
		return false
	}
	tables := []string{g_conf.Alarm_table, g_conf.Device_table, g_conf.Device_table_bool, g_conf.Status_table}
	// tables := []string{g_conf.Status_table}
	for i := 0; i < len(tables); i++ {
		dststr := fmt.Sprintf("truncate table %s", tables[i])
		fmt.Println(dststr)
		g_client.exec_sql(dststr)
	}

	return true
}

type myDatabase struct {
	name   string
	user   string
	passwd string
	db     string
	host   string
}

var mydb []myDatabase = []myDatabase{
	{"test", "ijmaster", "ijjazhang", "ijdbs", "tcp(10.10.9.13:3306)"},
	{"irext", "ijmaster", "ijjazhang", "irext_new", "tcp(10.135.255.204:3307)"},
	{"wateraffairs", "root", "www.galaxywind.com_ifanscloud.com", "wateraffairs_info", "tcp(10.133.17.56:3306)"},
}

func go_show_database(v ...interface{}) bool {
	if len(v) >= 3 && len(v[2].([]string)) > 0 {
		dstdb := v[2].([]string)[0]
		for i := 0; i < len(mydb); i++ {
			if dstdb == mydb[i].name {
				fmt.Println("database user=", mydb[i].user, "passwd=", mydb[i].passwd, "db=", mydb[i].db, "host=", mydb[i].host)
				return true
			}
		}
	}
	for i := 0; i < len(mydb); i++ {
		fmt.Println("name=", mydb[i].name, "database user=", mydb[i].user, "passwd=", mydb[i].passwd, "db=", mydb[i].db, "host=", mydb[i].host)
	}

	return true
}

func go_connect(v ...interface{}) bool {
	if len(v) >= 3 && len(v[2].([]string)) > 0 {
		dstdb := v[2].([]string)[0]
		i := 0
		for i = 0; i < len(mydb); i++ {
			if dstdb == mydb[i].name {
				g_client = NewClient("ijmaster", "ijjazhang", "ijdbs", "tcp(10.10.9.13:3306)") //测试密码服务器
				break
			}
		}
		if i == len(mydb) {
			fmt.Println("err database name=", dstdb)
			go_show_database()
			return false
		}
	} else {
		g_client = NewClient(g_conf.Username, g_conf.Passwd, g_conf.Database, "tcp("+g_conf.Host+":"+g_conf.Port+")")
	}
	err := g_client.Conn()
	if err != nil {
		fmt.Println("\rfailed to conn to mysql err=", err)
	} else {
		fmt.Println("\rconn mysql successful host=", "tcp("+g_conf.Host+":"+g_conf.Port+")")
	}

	return true
}

func go_modify_user(v ...interface{}) bool {
	if len(v) < 3 {
		fmt.Println("\r\n参数错误")
	}
	args := v[2].([]string)
	if len(args) <= 0 {
		fmt.Println("username=", g_conf.Username)
	} else {
		g_conf.Username = args[0]
	}

	return true
}

func go_modify_passwd(v ...interface{}) bool {
	if len(v) < 3 {
		fmt.Println("\r\n参数错误")
	}
	args := v[2].([]string)
	if len(args) <= 0 {
		fmt.Println("passwd=", g_conf.Passwd)
	} else {
		g_conf.Passwd = args[0]
	}

	return true
}

func go_modify_database(v ...interface{}) bool {
	if len(v) < 3 {
		fmt.Println("\r\n参数错误")
	}
	args := v[2].([]string)
	if len(args) <= 0 {
		fmt.Println("database=", g_conf.Database)
	} else {
		g_conf.Database = args[0]
	}

	return true
}

func go_modify_host(v ...interface{}) bool {
	if len(v) < 3 {
		fmt.Println("\r\n参数错误")
	}
	args := v[2].([]string)
	if len(args) <= 0 {
		fmt.Println("host=", g_conf.Host)
	} else {
		g_conf.Host = args[0]
	}

	return true
}

func go_modify_port(v ...interface{}) bool {
	if len(v) < 3 {
		fmt.Println("\r\n参数错误")
	}
	args := v[2].([]string)
	if len(args) <= 0 {
		fmt.Println("port=", g_conf.Port)
	} else {
		g_conf.Port = args[0]
	}

	return true
}

func go_sql(v ...interface{}) bool {
	if g_client == nil {
		fmt.Println("\rplease connect first")
		return false
	}
	if len(v) < 3 {
		return false
	}
	sqlstr := ""
	arg := v[2].([]string)
	arglen := len(arg)
	for i := 0; i < arglen; i++ {
		if i != 0 {
			sqlstr += " "
		}
		sqlstr += arg[i]
	}
	fmt.Println("\rsqlstr=", sqlstr)
	if sqlstr[:6] == "select" ||
		sqlstr[:4] == "show" ||
		sqlstr[:4] == "desc" {
		//result set sql
		g_client.query_sql(sqlstr)
	} else {
		//no result set sql
		g_client.exec_sql(sqlstr)
	}

	return true
}

var (
	g_client *Client
	g_conf   *JsonConf
)

//config
type JsonConf struct {
	//sql 账号配置
	Username string `json:"username"`
	Passwd   string `json:"passwd"`
	Database string `json:"database"`
	Host     string `json:"host"`
	Port     string `json:"port"`
	//使用全局配置
	Alarm_table       string `json:"alarm_table"`
	Device_table      string `json:"device_table"`
	Device_table_bool string `json:"device_table_bool"`
	Status_table      string `json:"status_table"`
	TimeLayout        string `json:"timeLayout"`
	//报警表时间
	Alarm_begin_year  int `json:"alarm_begin_year"`
	Alarm_begin_month int `json:"alarm_begin_month"`
	Alarm_begin_day   int `json:"alarm_begin_day"`
	Alarm_begin_hour  int `json:"alarm_begin_hour"`
	Alarm_begin_min   int `json:"alarm_begin_min"`

	Alarm_end_year  int `json:"alarm_end_year"`
	Alarm_end_month int `json:"alarm_end_month"`
	Alarm_end_day   int `json:"alarm_end_day"`
	Alarm_end_hour  int `json:"alarm_end_hour"`
	Alarm_end_min   int `json:"alarm_end_min"`

	//报警间隔
	Alarm_interval int `json:"alarm_interval_time"`

	Status_begin_year  int `json:"status_begin_year"`
	Status_begin_month int `json:"status_begin_month"`
	Status_begin_day   int `json:"status_begin_day"`
	Status_begin_hour  int `json:"status_begin_hour"`
	Status_begin_min   int `json:"status_begin_min"`

	Status_end_year  int `json:"status_end_year"`
	Status_end_month int `json:"status_end_month"`
	Status_end_day   int `json:"status_end_day"`
	Status_end_hour  int `json:"status_end_hour"`
	Status_end_min   int `json:"status_end_min"`

	Status_interval int `json:"status_interval"`

	//水站设备数组
	WaterArray []WaterItem `json:"waterArray"`

	Line_num []int `json:"Line_num"`
}

func init() {
	//初始化下随机种子
	rand.Seed(time.Now().Unix())
	ele := command.RegisterElement("mysql", "操作数据库相关")
	ele.RegisterCmd("modify_user", go_modify_user, "修改数据库用户,一个字符串参数,不加\"")
	ele.RegisterCmd("modify_passwd", go_modify_passwd, "修改数据库密码,一个字符串参数,不加\"")
	ele.RegisterCmd("modify_database", go_modify_database, "修改数据库名称,一个字符串参数,不加\"")
	ele.RegisterCmd("modify_host", go_modify_host, "修改数据库ip(x.x.x.x),一个字符串参数,不加\"")
	ele.RegisterCmd("modify_port", go_modify_port, "修改数据库端口, 一个整数参数")
	ele.RegisterCmd("connect", go_connect, "去连接下数据库")
	ele.RegisterCmd("show_database", go_show_database, "显示相应数据库信息,避免记不住")
	ele.RegisterCmd("device_add", go_device_add, "往数据库添加设备状态模拟数据")
	ele.RegisterCmd("device_alarm_add", go_device_alarm_add, "往数据库添加设备报警模拟数据")
	ele.RegisterCmd("device_cur_status_add", go_device_cur_status_add, "往数据库添加设备当前状态模拟数据")
	ele.RegisterCmd("device_mysql_clean", go_device_mysql_clean, "数据库清除")
	ele.RegisterCmd("sql", go_sql, "执行sql语句")
	//默认配置
	g_conf = &JsonConf{
		Username:          "root",
		Passwd:            "www.galaxywind.com_ifanscloud.com",
		Database:          "wateraffairs_info",
		Host:              "10.134.10.1",
		Port:              "3306",
		Alarm_table:       "tbl_wateraffairs_alarm_history",
		Device_table:      "tbl_wateraffairs_device_status_history",
		Device_table_bool: "tbl_wateraffairs_device_history_bool",
		Status_table:      "tbl_wateraffairs_status_info",
		TimeLayout:        "2006-01-02 15:04:05",

		Alarm_begin_year:  2020,
		Alarm_begin_month: 8,
		Alarm_begin_day:   10,
		Alarm_begin_hour:  0,
		Alarm_begin_min:   0,

		Alarm_end_year:  2020,
		Alarm_end_month: 8,
		Alarm_end_day:   30,
		Alarm_end_hour:  23,
		Alarm_end_min:   0,

		Alarm_interval: 5 * 60,

		Status_begin_year:  2020,
		Status_begin_month: 8,
		Status_begin_day:   10,
		Status_begin_hour:  0,
		Status_begin_min:   0,

		Status_end_year:  2020,
		Status_end_month: 8,
		Status_end_day:   30,
		Status_end_hour:  23,

		Status_interval: 4 * 60 * 60,

		WaterArray: []WaterItem{
			{240100017667, 8},
			{267000000002, 10},
			{267000000011, 11},
			{267000000015, 12},
		},
		Line_num: []int{
			1, 2, 3, 4,
		},
	}
	ele.RegisterConf(g_conf)
}
